﻿using lwzlz.Application.Information;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace lwzlz.Application.Model
{
    public class DataTableDal
    {
        private static DataTableDal _instance;
        private string conn = ConfigurationManager.ConnectionStrings["CMS"].ToString();
        //public static string conn = System.Web.HttpContext.Current.Session["sqlstring"].ToString();
        public static DataTableDal GetInstance()
        {

            return _instance ?? (_instance = new DataTableDal());
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="db">数据库名称</param>
        /// <returns></returns>
        public IList<DataTableInfo> GetList(string db, string searchTable, int tag = 0)
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.Append(" select sys.tables.NAME, ");
            strSql.Append(" (SELECT sys.extended_properties.VALUE FROM sys.extended_properties ");
            strSql.Append(" where minor_ID='0' ");
            strSql.Append(" and sys.extended_properties.Major_ID=sys.tables.Object_ID  ");
            strSql.Append(" and sys.extended_properties.name='MS_Description' ");
            strSql.Append(" ) AS Description");
            strSql.Append(" from sys.tables ");
            if (tag == 0)
                strSql.AppendFormat(" where sys.tables.NAME like '%{0}%' or ", searchTable);
            else
                strSql.AppendFormat(" where sys.tables.NAME='{0}' or ", searchTable);
            strSql.Append(" cast((SELECT sys.extended_properties.VALUE FROM sys.extended_properties ");
            strSql.Append(" where minor_ID='1' ");
            strSql.Append(" and sys.extended_properties.Major_ID=sys.tables.Object_ID  ");
            strSql.Append(" and sys.extended_properties.name='MS_Description') as varchar(100)) ");
            strSql.AppendFormat(" like '%{0}%' ", searchTable);
            strSql.Append(" order by sys.tables.NAME; ");
            DataSet ds1 = null;
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds1 = new DataSet();

                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds1);
                connstring.Close();
            }
            var list = new List<DataTableInfo>();
            if (Utils.HasMoreRow(ds1))
            {
                list.AddRange(from DataRow dr in ds1.Tables[0].Rows select DataRowToModel(dr));
            }
            return list;
        }
        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="row">数据行</param>
        /// <returns></returns>
        private DataTableInfo DataRowToModel(DataRow row)
        {
            var model = new DataTableInfo();
            if (row != null)
            {
                if (!string.IsNullOrEmpty(row["Name"].ToString()))
                {
                    model.Name = row["Name"].ToString();
                }
                if (!string.IsNullOrEmpty(row["Description"].ToString()))
                {
                    model.Description = row["Description"].ToString();
                }
            }
            return model;
        }
    }
}